-- =============================================
-- This procedure figures out which menu items need to be regenerated
-- when item specified as @InNavMenuID is changed.
--
-- Modifications
-- 06/19/2003  E.Tatsui   Created
-- =============================================

CREATE                    PROCEDURE amsp_CMGetNavMenuToRegenerate
  @InNavMenuID numeric
AS
  DECLARE
    @MinDepth numeric,
    @NavMenuID numeric,
    @PostFuseURL varchar(255)

  CREATE TABLE #Changed (NavMenuID numeric, ParentNavMenuID numeric, CategoryDepth numeric, PostFuseURL varchar(255))

  if object_id('tempdb..#Regenerate') is null
    CREATE TABLE #Regenerate (NavMenuID numeric)

  -- First put in our boy
  INSERT INTO #Changed(NavMenuID, ParentNavMenuID, CategoryDepth)
  SELECT a.NavMenuID, a.ParentNavMenuID, a.CategoryDepth
    FROM Nav_Menu a WITH (NOLOCK)
   WHERE a.NavMenuID = @InNavMenuID
  
  -- then, let's go down from here to get all descendants
  WHILE @@RowCount > 0
    INSERT INTO #Changed(NavMenuID, ParentNavMenuID, CategoryDepth)
    SELECT b.NavMenuID, b.ParentNavMenuID, b.CategoryDepth
      FROM #Changed a, Nav_Menu b WITH (NOLOCK) LEFT OUTER JOIN #Changed c
        ON b.NavMenuID = c.NavMenuID
     WHERE a.NavMenuID = b.ParentNavMenuID
       AND c.NavMenuID IS NULL
       AND b.WorkflowStatusCode = 'P'

  -- make sure we start with @@Rowcount > 0, even if our boy had no descendants
  SELECT @MinDepth = 1

  -- and then, as long as we are getting records, let's go directly back up
  WHILE @@RowCount > 0
    INSERT INTO #Changed(NavMenuID, ParentNavMenuID, CategoryDepth)
    SELECT b.NavMenuID, b.ParentNavMenuID, b.CategoryDepth
      FROM #Changed a, Nav_Menu b WITH (NOLOCK) LEFT OUTER JOIN #Changed c
        ON b.NavMenuID = c.NavMenuID
     WHERE (b.NavMenuID = a.ParentNavMenuID OR b.ParentNavMenuID = a.ParentNavMenuID)
       AND c.NavMenuID IS NULL
       AND (b.WorkflowStatusCode = 'P' OR b.WorkflowStatusCode = 'D')

  -- now, we have a table of all of the items that could have changed. Find the highest level item
  -- (lowest depth) that changed according to the Nav_Menu_Log
  -- Note: Include WorkflowStatusCode = 'D' so that if one nav item is about to be deleted,
  -- we can regenerate items affected by the deletion.
  SELECT @MinDepth = IsNULL(Min(a.CategoryDepth), 999)
    FROM Nav_Menu a WITH (NOLOCK), #Changed b LEFT OUTER JOIN Nav_Menu_Setup_Log c WITH (NOLOCK)
      ON b.NavMenuID = c.NavMenuID
   WHERE a.NavMenuID = b.NavMenuID
     AND (a.Name != IsNull(c.Name, '')
      OR a.Title != IsNull(c.Title, '')
      OR a.ParentNavMenuID != IsNull(c.ParentNavMenuID, 0)
      OR a.AncestorNavMenuID != IsNull(c.AncestorNavMenuID, 0)
      OR a.CategoryDepth != IsNull(c.CategoryDepth, 0)
      OR IsNULL(a.MembersOnlyFlag, '') != IsNull(c.MembersOnlyFlag, '')
      OR IsNull(a.PostFuseURL,'') != IsNull(c.PostFuseURL, '')
      OR IsNull(a.FilePath,'') != IsNull(c.FilePath,'')
      OR a.WorkflowStatusCode = 'D')

  -- ok, so now that we have the lowest depth, we can get the parent of that guy (or
  -- that guy if he is the ultimate ancestor), then get everyone on down.
  INSERT INTO #Regenerate
  SELECT DISTINCT ISNULL(ParentNavMenuID, NavMenuID) AS NavMenuID
    FROM #Changed
   WHERE CategoryDepth = @MinDepth
     
  -- then, as long as we are getting records, let's keep going down
  WHILE @@RowCount > 0
    
  BEGIN
    INSERT INTO #Regenerate(NavMenuID)
    SELECT b.NavMenuID
      FROM #Regenerate a, Nav_Menu b WITH (NOLOCK) LEFT OUTER JOIN #Regenerate c
        ON b.NavMenuID = c.NavMenuID
           LEFT OUTER JOIN #Changed d
        ON b.NavMenuID = d.NavMenuID
     WHERE a.NavMenuID = b.ParentNavMenuID
       AND c.NavMenuID IS NULL
       AND b.WorkflowStatusCode = 'P'
  END

  BEGIN TRANSACTION

    DELETE Nav_Menu_Setup_Log
      FROM Nav_Menu_Setup_Log a, #Regenerate b
     WHERE a.NavMenuID = b.NavMenuID

    INSERT INTO Nav_Menu_Setup_Log (
           NavMenuID,
           Name,
           Title,
           ParentNavMenuID,
           AncestorNavMenuID,
           CategoryDepth,
           MembersOnlyFlag,
           PostFuseURL,
           FilePath)
    SELECT a.NavMenuID,
           a.Name,
             a.Title,
           a.ParentNavMenuID,
           IsNull(a.AncestorNavMenuID, a.NavMenuID),
           a.CategoryDepth,
           a.MembersOnlyFlag,
           a.PostFuseURL,
           a.FilePath
      FROM Nav_Menu a WITH (NOLOCK), #Regenerate b
     WHERE a.NavMenuID = b.NavMenuID

  COMMIT TRANSACTION

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetNavMenuToRegenerate] TO [IMIS]
GO